package net.sppan.base.dao;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import net.sppan.base.dao.support.IBaseDao;
import net.sppan.base.entity.ExContentView;
import net.sppan.base.entity.ExuserContent;
import net.sppan.base.entity.UsertTask;

public interface IExuserContentDao extends IBaseDao<ExuserContent, Long>{

	
	/**
	 * 
	 * @param userId
	 * @param type
	 * @param status
	 * @param pageable
	 * 
	 * nativeQuery = true 原生态sql 时分页查询 最后得加上 ORDER BY ?#{#pageable}
	 * @return
	 */
	@Query(value = "SELECT new net.sppan.base.entity.ExContentView( tr.id , tr.exuserId , tr.dyContentId as dyContentId, "
		    +"tr.exResult, tr.exTime, tr.exDesc, cn.publisherDyId, "
		    +"cn.uploadDate, cn.contentText, cn.contentUrl, "
		    +"cn.videoDisplay, cn.pixelWidth, cn.pixelHeight, "
		    +"cn.duration, cn.fileSize, cn.praiseNum, cn.trampleNum, "
		    +"cn.contentType, cn.isApproved )"
			+ "FROM  ExuserContent tr ,ContentUnreviewed  cn "
			+ "WHERE tr.dyContentId = cn.dyContentId AND tr.exuserId = :userId AND tr.exResult=:result ORDER  BY cn.uploadDate DESC ", 
	        countQuery = "select count(*) FROM   ExuserContent tr ,ContentUnreviewed  cn "
			+ "WHERE tr.dyContentId = cn.dyContentId  and tr.exuserId = :userId  AND tr.exResult=:result  ")
	public Page<ExContentView> findExuserContentList(@Param("userId") Long userId, @Param("result") String status,Pageable pageable);

	
	/**
	 * 
	 * @param userId
	 * @param type
	 * @param status
	 * @param pageable
	 * 
	 * nativeQuery = true 原生态sql 时分页查询 最后得加上 ORDER BY ?#{#pageable}
	 * @return
	 */
	@Query(value = "SELECT new net.sppan.base.entity.ExContentView( tr.id , tr.exuserId , tr.dyContentId as dyContentId, "
		    +"tr.exResult, tr.exTime, tr.exDesc, cn.publisherDyId, "
		    +"cn.uploadDate, cn.contentText, cn.contentUrl, "
		    +"cn.videoDisplay, cn.pixelWidth, cn.pixelHeight, "
		    +"cn.duration, cn.fileSize, cn.praiseNum, cn.trampleNum, "
		    +"cn.contentType, cn.isApproved )"
			+ "FROM  ExuserContent tr ,ContentUnreviewed  cn "
			+ "WHERE tr.dyContentId = cn.dyContentId  ORDER  BY cn.uploadDate DESC ", 
	        countQuery = "select count(*) FROM   ExuserContent tr ,ContentUnreviewed  cn "
			+ "WHERE tr.dyContentId = cn.dyContentId ")
	public Page<ExContentView> findExuserContentListHis(Pageable pageable);

	
	
	@Query(value= "SELECT u.id as exUserId,t.num as incompleteTaskNum FROM  tr_user u LEFT JOIN "
			   + " (SELECT c.exuser_id,count(*) as num FROM  tr_exuser_content c WHERE c.ex_Result=:status GROUP BY c.exuser_id  ) t ON u.id =t.exuser_id WHERE u.id<>1" ,nativeQuery = true)
	public List<Object[]> findExuserContentList(@Param("status") String status);


}
